package com.keehin.migrate;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;

import com.keehin.Database;

public class Migrater {
	private static final int FIELD_MEMO_SIZE = 8;
	protected Database srcDB;
	protected Database destDB;
	protected HashMap<String, String> typeMap;
	protected ResultSet dataset;
	protected ResultSetMetaData md;
	protected int srcTotalRecord;
	protected int destTotalRecord;
	private PreparedStatement pstmt;

	public Migrater(String srcPath, String destPath, String user, String pwd) {
		try {
			srcDB = new Database(new com.hxtt.sql.paradox.ParadoxDriver(),
					"jdbc:paradox:/" + srcPath + "?charSet=MS874", null, null);
			if (srcDB != null)
				System.out.println(srcDB + " OK");
			destDB = new Database(new com.mysql.jdbc.Driver(), "jdbc:mysql://"
					+ destPath + "?characterEncoding=tis620", user, pwd);

			if (destDB != null)
				System.out.println(destDB + " OK");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		typeMap = new HashMap<String, String>();
		typeMap.put("TIMESTAMP", "DATE");
		typeMap.put("CURRENCY", "FLOAT");
		typeMap.put("LONGVARCHAR", "VARCHAR");
	}

	public void generateSQL(String tableName) throws SQLException {
		md = dataset.getMetaData();
		int colCount = md.getColumnCount();
		String fieldName;
		String fieldType;
		int fieldSize;

		StringBuffer buff = new StringBuffer();
		buff.append("create table " + tableName + "(\n");
		for (int col = 1; col <= colCount; col++) {
			fieldName = md.getColumnName(col).replace(' ', '_');
			fieldType = md.getColumnTypeName(col);
			if (typeMap.get(fieldType) != null)
				fieldType = typeMap.get(fieldType);
			fieldSize = md.getColumnDisplaySize(col);
			buff.append(String.format("\t%-25s%s", fieldName, fieldType));
			if (fieldType.indexOf("CHAR") > -1) {
				if (fieldSize == 0)
					fieldSize = FIELD_MEMO_SIZE;
				buff.append("(" + fieldSize + ")");
			}
			if (col < colCount)
				buff.append(",");
			buff.append("\n");
		}
		buff.append(") DEFAULT CHARACTER SET=tis620 COLLATE=tis620_thai_ci;");
		System.out.println(buff);
		destDB.createSQL("create", buff.toString());
		buff = new StringBuffer();
		buff.append("insert into " + tableName);
		buff.append("\nvalues (");
		for (int col = 1; col <= colCount; col++) {
			buff.append("?");
			if (col < colCount)
				buff.append(",");
		}
		buff.append("); ");
		destDB.createSQL("insert", buff.toString());
	}

	public void transfer(String tableName, boolean isDrop) {
		long startTime = System.currentTimeMillis();
		try {
			System.out.println("Table : " + tableName);
			if (isDrop) {
				System.out.println("Drop old table " + tableName);
				destDB.executeUpdate("drop table " + tableName);
			}
			DatabaseMetaData dbMetaData = destDB.getConnection().getMetaData();
			ResultSet rs = dbMetaData.getTables(null, null, tableName, null);
			System.out.println("select *");
			srcDB.createSQL("select", "select * from " + tableName);
			dataset = srcDB.querySqlMap("select", null);
			this.generateSQL(tableName);
			if (rs == null || !rs.next()) {
				System.out.println("Create Table");
				destDB.execSqlMap("create", null);
			} else {
				System.out.println("Table Exists");
			}
			rs.close();
			System.out.print("Total " + tableName + " on MySQL : \t");
			rs = destDB.executeQuery("select count(*) from " + tableName);
			rs.next();
			destTotalRecord = rs.getInt(1);
			System.out.println(destTotalRecord);
			// destDB.executeUpdate("set autocommit=0");
			destDB.executeUpdate("start transaction");
			int row = 0;
			while (dataset.next()) {
				row++;

				if (row % 1000 == 0) {
					System.out.print("#");
					if (row % 100000 == 0)
						System.out.printf("%,10d\n", row);
					else if (row % 50000 == 0)
						System.out.println();
				}
				if (row > destTotalRecord) {
					loadData();
				}
			}
			System.out.printf(" = %,d records \n", row);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		destDB.executeUpdate("commit");
		long endTime = System.currentTimeMillis();
		System.out.printf("Elapse Time %9.3f seconds\n",
				(endTime - startTime) / 1000.0);
	}

	public void loadData() {
		String fieldType;
		pstmt = destDB.getStatement("insert");

		try {
			for (int col = 1; col <= md.getColumnCount(); col++) {
				fieldType = md.getColumnTypeName(col);
				if (fieldType.indexOf("CHAR") > -1) {
					if (fieldType.equals("LONGVARCHAR"))
						pstmt.setString(col, null);
					else
						pstmt.setString(col, dataset.getString(col));
				} else if (fieldType.indexOf("TIME") > -1)
					pstmt.setDate(col, dataset.getDate(col));
				else if (fieldType.indexOf("INT") > -1)
					pstmt.setInt(col, dataset.getInt(col));
				else if (fieldType.equals("CURRENCY"))
					pstmt.setFloat(col, dataset.getFloat(col));
				else if (fieldType.equals("DOUBLE"))
					pstmt.setDouble(col, dataset.getDouble(col));
				else
					pstmt.setObject(col, dataset.getObject(col));
				// pstmt.setObject(col, null);
			}
			// System.out.println("insert" + pstmt);
			destDB.execSqlMap("insert", null);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) throws SQLException {
		if (args.length > 0) {
			String tableName = args[0];
			boolean isDrop = args.length > 1 && args[1].equals("drop");
			String srcPath = "c://paradox";
			if (args.length > 2)
				srcPath = args[2];
			String destPath = "ke";
			if (args.length > 3)
				destPath = args[3];
			String user = "tom";
			if (args.length > 4)
				user = args[4];
			String pwd = "yesiam";
			if (args.length > 5)
				pwd = args[5];
			Migrater migrater = new Migrater(srcPath, destPath, user, pwd);
			migrater.transfer(tableName, isDrop);
		} else {
			System.out
					.println("HELP : Migrater TABLE [DROP] [SRC] [DEST] [USER] [PASSWORD]");
			System.out
					.println("Example java Migrater STKBODY drop c://mbs//dat localhost/ke user password");
		}
	}
}
